﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.XWPF.UserModel;
using System.Data;
using System.Runtime.CompilerServices;

Console.WriteLine("Hello, World!");

//声明文件路径字段，存储文件路径对应目标文件
var filePath = @"C:\Users\29561\Desktop\prms.xlsx";
//判断文件是否存在，否则会报错
if (!File.Exists(filePath))
{
    return;
}
//根据上面路径读取文件
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

//根据文件流创建excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fs);

//尝试获取Excel第一个sheet 
var sheet = workbook.GetSheetAt(0);
//判断是否获取到 sheet 
if (sheet != null)
{
    DataTable dtTable = new DataTable();

    //获取第一行的列
    var column = sheet.GetRow(0);
    for (int i = 0; i < column.Count(); i++)
    {
        dtTable.Columns.Add(column.GetCell(i).ToString());
        //输出每个单元格的数据
        Console.WriteLine($"第一行数据：第 {i} 个数据值：{column.GetCell(i).ToString()}");
    }

    //LastRowNum 是当前表的总行数-1（注意）
    for (int i = 1; i <= sheet.LastRowNum - 1; i++)
    {
        var dataRow = dtTable.NewRow();
        IRow row = sheet.GetRow(i);  //读取当前行数据

        for (int j = 0; j < dtTable.Columns.Count; j++)
        {
            //读取该行的第j列数据
            dataRow[j] = row.GetCell(j);
        }
        dtTable.Rows.Add(dataRow);
    }

    var res = JsonConvert.SerializeObject(dtTable);
    var i18nData = JsonConvert.DeserializeObject<List<i18n>>(res);
    var cnList = i18nData.Where(m => m.STATUS.ToUpper() == "ACTIVE" && m.LANGUAGE.ToUpper() == "CN").DistinctBy(m => m.CONTENT_TEXT);
    var enList = i18nData.Where(m => m.STATUS.ToUpper() == "ACTIVE" && m.LANGUAGE.ToUpper() == "EN").DistinctBy(m => m.CONTENT_TEXT);

    JObject cnObj = new JObject();
    foreach (var item in cnList)
    {
        var key = $"lang.{item.LABEL_CATEGORY}.{item.LABEL_ALIAS}";
        if (!cnObj.ContainsKey(key))
        {
            cnObj.Add(key, item.CONTENT_TEXT);
        }
    }
    JObject enObj = new JObject();
    foreach (var item in enList)
    {
        var key = $"lang.{item.LABEL_CATEGORY}.{item.LABEL_ALIAS}";
        if (!enObj.ContainsKey(key))
        {
            enObj.Add(key, item.CONTENT_TEXT);
        }
    }
    var cn = JsonConvert.SerializeObject(cnObj);
    var en = JsonConvert.SerializeObject(enObj);
}

public class i18n
{
    public string SYS_RRN { get; set; }//": "1.40794735092786E+18",
    public string OBJECT_SPACE { get; set; }//": "MYCIM",
    public string LABEL_ID { get; set; }//": "LBL_MAXLENGTH",
    public string LABEL_CATEGORY { get; set; }//": "form",
    public string LABEL_SUB_CATEGORY { get; set; }//": null,
    public string DESCRIPTION { get; set; }//": "Max Length",
    public string LABEL_ALIAS { get; set; }//": "maxLength",
    public string MSG_CODE { get; set; }//": null,
    public string SYS_USED_FLG { get; set; }//": "0",
    public string STATUS { get; set; }//": "ACTIVE",
    public string CREATE_USER { get; set; }//": null,
    public string CREATE_TIME { get; set; }//": "07-7月-2021",
    public string UPDATE_USER { get; set; }//": null,
    public string UPDATE_TIME { get; set; }//": "07-7月-2021",
    public string CONTENT_SYS_RRN { get; set; }//": "1.40794735092786E+18",
    public string LABEL_RRN { get; set; }//": "1.40794735092786E+18",
    public string LANGUAGE { get; set; }//": "CN",
    public string CONTENT_TEXT { get; set; }//": "最大长度",
    public string FULL_CONTEXT { get; set; }//": null

} 